"""Add performance indexes for product tables

Revision ID: 20241005_add_indexes
Revises: 20241005_optimize_existing
Create Date: 2025-10-05 18:37:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = '20241005_add_indexes'
down_revision: Union[str, Sequence[str], None] = '20241005_optimize_existing'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema - Phase 3: Add performance indexes"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Product table indexes
    op.create_index('idx_product_name', 'products', ['name'], unique=False)
    op.create_index('idx_product_category_id', 'products', ['category_id'], unique=False)
    op.create_index('idx_product_price', 'products', ['price'], unique=False)
    op.create_index('idx_product_stock', 'products', ['stock'], unique=False)
    op.create_index('idx_product_sales', 'products', ['sales'], unique=False)
    op.create_index('idx_product_category_active_price', 'products', ['category_id', 'is_active', 'price'], unique=False)
    op.create_index('idx_product_active_featured', 'products', ['is_active', 'is_featured'], unique=False)
    op.create_index('idx_product_created_at', 'products', ['created_at'], unique=False)

    # ProductCategory table indexes
    op.create_index('idx_category_name', 'product_categories', ['name'], unique=False)
    op.create_index('idx_category_parent_id', 'product_categories', ['parent_id'], unique=False)
    op.create_index('idx_category_is_active', 'product_categories', ['is_active'], unique=False)
    op.create_index('idx_category_sort_order', 'product_categories', ['sort_order'], unique=False)

    # ProductDetail table indexes
    op.create_index('idx_detail_is_active', 'product_details', ['is_active'], unique=False)
    op.create_index('idx_detail_meta_title', 'product_details', ['meta_title'], unique=False)

    # ProductImage table indexes
    op.create_index('idx_image_is_main', 'product_images', ['is_main'], unique=False)
    op.create_index('idx_image_product_main', 'product_images', ['product_id', 'is_main'], unique=False)
    op.create_index('idx_image_sort_order', 'product_images', ['sort_order'], unique=False)

    # ProductWarranty table indexes
    op.create_index('idx_warranty_type', 'product_warranty', ['warranty_type'], unique=False)
    op.create_index('idx_warranty_is_active', 'product_warranty', ['is_active'], unique=False)

    # ProductService table indexes
    op.create_index('idx_service_type', 'product_services', ['service_type'], unique=False)
    op.create_index('idx_service_is_active', 'product_services', ['is_active'], unique=False)

    # ProductShipping table indexes
    op.create_index('idx_shipping_fee', 'product_shipping', ['shipping_fee'], unique=False)
    op.create_index('idx_shipping_free_limit', 'product_shipping', ['free_shipping_limit'], unique=False)

    # Attribute definitions indexes
    op.create_index('idx_attr_def_code', 'attribute_definitions', ['code'], unique=False)
    op.create_index('idx_attr_def_is_active', 'attribute_definitions', ['is_active'], unique=False)

    # Attribute values indexes
    op.create_index('idx_attr_value_attribute_id', 'attribute_values', ['attribute_id'], unique=False)
    op.create_index('idx_attr_value_is_active', 'attribute_values', ['is_active'], unique=False)

    # Specification groups indexes
    op.create_index('idx_spec_group_is_active', 'specification_groups', ['is_active'], unique=False)

    # Specification definitions indexes
    op.create_index('idx_spec_def_group_id', 'specification_definitions', ['group_id'], unique=False)
    op.create_index('idx_spec_def_code', 'specification_definitions', ['code'], unique=False)
    op.create_index('idx_spec_def_is_active', 'specification_definitions', ['is_active'], unique=False)

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Drop specification definition indexes
    op.drop_index('idx_spec_def_is_active', table_name='specification_definitions')
    op.drop_index('idx_spec_def_code', table_name='specification_definitions')
    op.drop_index('idx_spec_def_group_id', table_name='specification_definitions')

    # Drop specification group indexes
    op.drop_index('idx_spec_group_is_active', table_name='specification_groups')

    # Drop attribute value indexes
    op.drop_index('idx_attr_value_is_active', table_name='attribute_values')
    op.drop_index('idx_attr_value_attribute_id', table_name='attribute_values')

    # Drop attribute definition indexes
    op.drop_index('idx_attr_def_is_active', table_name='attribute_definitions')
    op.drop_index('idx_attr_def_code', table_name='attribute_definitions')

    # Drop shipping indexes
    op.drop_index('idx_shipping_free_limit', table_name='product_shipping')
    op.drop_index('idx_shipping_fee', table_name='product_shipping')

    # Drop service indexes
    op.drop_index('idx_service_is_active', table_name='product_services')
    op.drop_index('idx_service_type', table_name='product_services')

    # Drop warranty indexes
    op.drop_index('idx_warranty_is_active', table_name='product_warranty')
    op.drop_index('idx_warranty_type', table_name='product_warranty')

    # Drop image indexes
    op.drop_index('idx_image_sort_order', table_name='product_images')
    op.drop_index('idx_image_product_main', table_name='product_images')
    op.drop_index('idx_image_is_main', table_name='product_images')

    # Drop detail indexes
    op.drop_index('idx_detail_meta_title', table_name='product_details')
    op.drop_index('idx_detail_is_active', table_name='product_details')

    # Drop category indexes
    op.drop_index('idx_category_sort_order', table_name='product_categories')
    op.drop_index('idx_category_is_active', table_name='product_categories')
    op.drop_index('idx_category_parent_id', table_name='product_categories')
    op.drop_index('idx_category_name', table_name='product_categories')

    # Drop product indexes
    op.drop_index('idx_product_created_at', table_name='products')
    op.drop_index('idx_product_active_featured', table_name='products')
    op.drop_index('idx_product_category_active_price', table_name='products')
    op.drop_index('idx_product_sales', table_name='products')
    op.drop_index('idx_product_stock', table_name='products')
    op.drop_index('idx_product_price', table_name='products')
    op.drop_index('idx_product_category_id', table_name='products')
    op.drop_index('idx_product_name', table_name='products')

    # ### end Alembic commands ###